<?php
/**
 * Fichier contenant les requetes vers la table depense
 * @author Nicolas Genevaux
 * (c)nige 2008
 */
	define(DEPENSE_INSERT,'INSERT INTO depense (idobjet,idlieu,montant,description,datedepense,heuredepense,idcompte) VALUES ("$0","$1","$2","$3","$4","$5","$6")');
	define(DEPENSE_SELECT_WP,'SELECT depense.*,lieu.nom AS lieu, objet.nom AS objet,compte.libelle AS compte,ville.nom AS nomville FROM depense,lieu,objet,compte,ville WHERE lieu.idville=ville.idville AND lieu.idlieu=depense.idlieu AND objet.idobjet=depense.idobjet AND depense.idcompte=compte.idcompte ORDER BY depense.datedepense DESC,depense.heuredepense DESC,depense.montant DESC LIMIT $0,$1');
	define(DEPENSE_SELECT_WPC,'SELECT depense.*,lieu.nom AS lieu, objet.nom AS objet,compte.libelle AS compte,ville.nom AS nomville FROM depense,lieu,objet,compte,ville WHERE lieu.idville=ville.idville AND lieu.idlieu=depense.idlieu AND objet.idobjet=depense.idobjet AND depense.idcompte=compte.idcompte AND compte.idcompte="$2" ORDER BY depense.datedepense DESC,depense.heuredepense DESC,depense.montant DESC LIMIT $0,$1');
	define(DEPENSE_SELECT,'SELECT * FROM depense WHERE iddepense="$0"');
	define(DEPENSE_UPDATE,'UPDATE depense SET idobjet="$1",idlieu="$2",montant="$3",description="$4",datedepense="$5",heuredepense="$6" WHERE iddepense="$0"');
	define(DEPENSE_DELETE,'DELETE FROM depense WHERE iddepense="$0"');
	define(DEPENSE_BANQUE,'UPDATE depense SET encaisse=(encaisse+1)%2 WHERE iddepense="$0"');
	define(DEPENSE_TOUT,'SELECT SUM(montant) AS montant FROM depense');
	define(DEPENSE_ENCAISS,'SELECT SUM(montant) AS montant FROM depense WHERE encaisse="1"');
	define(DEPENSE_TOUT_C,'SELECT SUM(depense.montant) AS montant FROM depense,compte WHERE compte.idcompte=depense.idcompte AND compte.idcompte="$0"');
	define(DEPENSE_ENCAISS_C,'SELECT SUM(depense.montant) AS montant FROM depense,compte WHERE compte.idcompte=depense.idcompte AND depense.encaisse="1" AND compte.idcompte="$0"');
	define(DEPENSE_MOIS,'SELECT SUBSTRING(datedepense,1,6) AS mois,sum(montant) AS total FROM `depense` GROUP BY SUBSTRING(datedepense,1,6) ORDER BY SUBSTRING(datedepense,1,6) DESC LIMIT $0,$1');
	define(DEPENSE_MOIS_C,'SELECT SUBSTRING(datedepense,1,6) AS mois,sum(montant) AS total FROM `depense` WHERE idcompte="$2" GROUP BY SUBSTRING(datedepense,1,6) ORDER BY SUBSTRING(datedepense,1,6) DESC LIMIT $0,$1');
	define(DEPENSE_MOYENNE_DATE,'SELECT idobjet,SUM(montant)/$2 AS moyenne FROM depense WHERE datedepense>"$0" AND datedepense<"$1" GROUP BY idobjet');
	define(DEPENSE_SELECT_DATE,'SELECT depense.*,lieu.nom AS lieu, objet.nom AS objet,compte.libelle AS compte FROM depense,lieu,objet,compte WHERE lieu.idlieu=depense.idlieu AND objet.idobjet=depense.idobjet AND depense.idcompte=compte.idcompte AND datedepense LIKE "$0%"');
	define(DEPENSE_MOYENNE_DATE_C,'SELECT idobjet,SUM(montant)/$3 AS moyenne FROM depense WHERE datedepense>"$0" AND datedepense<"$1" AND idcompte="$2" GROUP BY idobjet');
	define(DEPENSE_SELECT_DATE_C,'SELECT depense.*,lieu.nom AS lieu, objet.nom AS objet,compte.libelle AS compte FROM depense,lieu,objet,compte WHERE lieu.idlieu=depense.idlieu AND depense.idcompte="$1" AND objet.idobjet=depense.idobjet AND depense.idcompte=compte.idcompte AND datedepense LIKE "$0%"');
	define(DEPENSE_HAVE_PARENT,'SELECT idparentobjet,nom AS objet FROM objet WHERE idobjet="$0"');
	
	define(DEPENSE_DETAIL_CAT,'SELECT ville.nom AS vilnom,lieu.nom AS lienom,SUM(depense.montant) AS total,depense.idlieu FROM depense,lieu,ville WHERE lieu.idville=ville.idville AND lieu.idlieu=depense.idlieu AND depense.idobjet="$0" AND depense.datedepense LIKE "$1%" AND depense.idcompte LIKE "$2" GROUP BY ville.nom,lieu.nom ORDER BY SUM(depense.montant) DESC');
	define(DEPENSE_SUM,'SELECT SUM(depense.montant) AS total FROM depense WHERE depense.idobjet="$0" AND depense.datedepense LIKE "$1%" AND depense.idcompte LIKE "$2" AND idlieu="$3"');
?>
